package airportCoordination;

import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.Lock;

import org.json.JSONObject;

import java.net.InetAddress;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

public class PackageHandler implements Runnable{

	private BlockingQueue<Cargo> mailbox;
	private List<PacketLog> logbox;
	private Lock lock;
	private Statement sql;
	
	private int monitor_port;
	
	private String SQL_CONNECTION = "jdbc:mysql:///snevTracker";
	private String SQL_USER = "snev";
	private String SQL_PASS = "snevpw";
	
	private final String AIRPORT_ON_BASE = "SELECT ip FROM Airports WHERE ip = '%s';";
	private final String INSERT_AIRPORT = "INSERT INTO Airports (ip, dimPort, evPort, esPort) VALUES ('%s', %d, %d, %d);";
	private final String UPDATE_AIRPORT = "UPDATE Airports SET dimPort=%d, evPort=%d, esPort=%d WHERE ip = '%s';";
	private final String DELETE_FLIGHTS = "DELETE FROM Flights WHERE ip ='%s';";
	private final String INSERT_FLIGHT = "INSERT INTO Flights (ip, flight) VALUES ('%s', %d);";
	private final String DELETE_AIRPORTS = "DELETE FROM Airports WHERE ip ='%s';";
	private final String AIRPORT_DIMPORT = "SELECT dimPort FROM Airports WHERE ip = '%s';";
	
	public PackageHandler(int mp, BlockingQueue<Cargo> queue, String sql_connection, String sql_user, String sql_pass, List<PacketLog> lb, Lock l)
	{
		SQL_CONNECTION = sql_connection;
		SQL_USER = sql_user;
		SQL_PASS = sql_pass;
		try{
			sql = DriverManager.getConnection(SQL_CONNECTION, SQL_USER, SQL_PASS).createStatement();
		}catch(Exception e){ System.out.print("FATAL ERROR. Error conecting database: " + e.getMessage() + "\n"); System.exit(666); }

		mailbox = queue;
		monitor_port = mp;
		logbox = lb;
		lock = l;
	}
	
	public void run()
	{
		while (true)
		{
			String msg=null;
			String source_ip=null;
			
			try {
				Cargo c = mailbox.poll(5, TimeUnit.MINUTES);
				if (c != null)
				{
					System.out.print("Message on mailbox...\n");
					msg = c.get_msg();
					source_ip = c.get_ip();
				}
			}
			catch(Exception e) { System.out.print("Error getting mailbox content: " + e.getMessage() + "\n"); continue; }
			if (msg==null)
				continue;
			
			
			try{ 
				JSONObject detail = new JSONObject(msg);
				String type = detail.getString("type");
				System.out.print("Packet type: " + type + "\n");

				if (type.equals("OK"))
				{
					System.out.print("Ok recived.\n");
					lock.lock();
					int pack = Collections.binarySearch(logbox, new PacketLog(detail.getInt("id"), source_ip, 0, ""));
					if (pack>=0)
						logbox.remove(pack);
					lock.unlock();
				}
				
				if (type.equals("ERROR"))
				{
					System.out.print("Error recived.\n");
					lock.lock();
					int pack = Collections.binarySearch(logbox, new PacketLog(detail.getInt("id"), source_ip, 0, ""));
					if (pack>=0)
						logbox.get(pack).set_error(true);
					lock.unlock();
				}
				
				if (type.equals("UPDATE"))
				{
					System.out.print("Update recived.\n");
					update(detail.getString("ip"), detail.getInt("id"), detail);
				}
				
				if (type.equals("UNPLUG"))
				{
					System.out.print("UnPlug recived.\n");
					unplug(detail.getString("ip"), detail.getInt("id"));
				}
				
				if (type.equals("PING"))
				{
					System.out.print("Ping recived. from " + source_ip + ", being localhost: " + Sender.toString(InetAddress.getByName("localhost")) + "\n");
					if (source_ip.equals(Sender.toString(InetAddress.getByName("localhost"))))
						Sender.ok("localhost", monitor_port, detail.getInt("id"));
					else
					{
						ResultSet rs = sql.executeQuery(String.format(AIRPORT_DIMPORT, source_ip));
						if (rs.first())
							Sender.ok(source_ip, rs.getInt("dimPort"), detail.getInt("id"));
					}
				}
			}
			catch(Exception e) { System.out.print("Error processing message: " + e.getMessage() + "\n"); if (lock.tryLock()) lock.unlock(); }
		}
	}
	
	void update(String ip, int id, JSONObject detail) throws Exception
	{
		
		int dimPort = detail.getInt("dimPort");
		int evPort = detail.getInt("evPort");
		int esPort = detail.getInt("esPort");
		int nFlights = detail.getInt("nFlights");
		boolean first_update;
		
		List<Integer> flights = new LinkedList<Integer>();
		for (int fn=0;nFlights>fn;fn++)
			flights.add(detail.getInt("fp" + Integer.toString(fn)));
		
		try{
			ResultSet rs = sql.executeQuery(String.format(AIRPORT_ON_BASE, ip));
			if (!rs.last())
			{
				sql.execute(String.format(INSERT_AIRPORT, ip, dimPort, evPort, esPort));
				first_update = true;
			}
			else
			{
				sql.execute(String.format(UPDATE_AIRPORT, dimPort, evPort, esPort, ip));
				sql.execute(String.format(DELETE_FLIGHTS, ip));
				first_update = false;
			}
			for (int fn=0; nFlights>fn;fn++)
				sql.execute(String.format(INSERT_FLIGHT, ip, flights.get(fn)));
		}
		catch (Exception e) { System.out.print("Error managing sql database: " + e.getMessage() + "\n"); Sender.error(ip, dimPort, id); return; }
		Sender.ok(ip, dimPort, id);
		Sender.Sync(ip, first_update);
	}
	
	void unplug(String ip, int id)
	{
		int port=0;
		
		try{
			ResultSet rs = sql.executeQuery(String.format(AIRPORT_DIMPORT, ip));
			if (!rs.first()) return;
				port = rs.getInt("dimPort");
			sql.execute(String.format(DELETE_FLIGHTS, ip));
			sql.execute(String.format(DELETE_AIRPORTS, ip));
		}
		catch (Exception e) { System.out.print("Error managing sql database: " + e.getMessage() + "\n"); Sender.error(ip, port, id); return; }
		Sender.ok(ip, port, id);
		Sender.Unplug(ip);
	}
}
